package com.example.goods_service.dao;

import com.example.goods_service.vo.*;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

@Mapper
@Repository
public interface GoodsDao_oyhl
{
    @Select("SELECT * FROM t_commodity_category where category_name like '%${category_name}%' ")
    List<TCommodityCategory>shop(String category_name);

    @Select("select value,id from search_records where userid='${userid}' ORDER BY time desc")
    List<SearchRecords>ls(int userid);

    @Insert("insert into search_records(value,userid,time) values('${value}','${userid}',NOW())")
    int indata(String value,int userid,String time);

    @Delete("delete from search_records where id='${id}'")
    int dele(int id);

    @Select("select * from search_records where userid='${userid}' and value='${value}'")
    Integer sel(int userid,String value);

    @Select("SELECT DISTINCT * FROM `t_commodity_category` a ,t_commodity b, t_picture c where a.id=b.category_id and b.commodity_oricture=c.id and a.category_name='${category_name}' ")
    List<TCommodity>ppp(String category_name);

    @Select("SELECT count(*)  FROM `t_commodity_category` a ,t_commodity b where a.id=b.category_id and a.category_name='${category_name}' ")
    int pppp(String category_name);

    @Select("select category_name from t_commodity_category where  category_name='${category_name}'")
    List<TCommodity> sel1(String category_name);

    @Select("SELECT DISTINCT * FROM `t_commodity_category` a ,t_commodity b, t_picture c where a.id=b.category_id and b.commodity_oricture=c.id and  b.commodity_description like '%${category_name}%' ")
    List<TCommodity>ppp1(String category_name);

    @Select("SELECT count(*) FROM `t_commodity_category` a ,t_commodity b where a.id=b.category_id and b.commodity_description like '%${category_name}%' ")
    int pppp1(String category_name);

    @Select("SELECT b.id , b.attribute from t_commodity_category a ,t_category_attribute b where a.id=b.attribute_id and category_name='${category_name}'")
    List<TCategoryAttribute>lsal(String category_name);

    @Select("SELECT * FROM t_attribute_details")
    List<TAttributeDetails>list();

    @Select("SELECT DISTINCT b.id,b.commodity_name,b.commodity_description,b.commodity_price,b.status,b.sales_volume,b.creation_time,b.release_time,b.category_id,c.picture_path_name" +
            " FROM `t_commodity_category` a ,t_commodity b,t_picture c where a.id=b.category_id  and b.commodity_oricture=c.id and  b.creation_time like '%${creation_time}%' and b.commodity_name like '%${commodity_name}%'")
    List<TCommodity>ssssss(String creation_time,String commodity_name);

    @Select("SELECT count(*) FROM `t_commodity_category` a ,t_commodity b,t_picture c where a.id=b.category_id  and b.commodity_oricture=c.id and  b.creation_time like '%${creation_time}%' and b.commodity_name like '%${commodity_name}%'")
    int ssssss1(String creation_time,String commodity_name);

    @Select("SELECT * FROM t_commodity_allocation")
    List<T_commodity_allocation>sssss();


    @Select("SELECT b.id, b.category_id , b.configuration_type from t_commodity_category a ,t_category_config b where a.id=b.category_id and category_name='${category_name}'")
    List<T_category_config>oooo(String category_name);

    @Select("SELECT * FROM t_config_details")
    List<T_config_details>oooo1();


    //筛选 属性 配置
    @Select("SELECT DISTINCT a.id,a.commodity_name,a.commodity_description,a.commodity_price,a.status,a.sales_volume,f.picture_path_name\n" +
            "FROM t_commodity a,t_selected_attribute b,t_category_config c,t_config_details d,`t_commodity_category` e, t_picture f,\n" +
            "t_selected_config h,t_category_attribute i,t_attribute_details j\n" +
            "where a.id=b.goods_id and c.id=b.attribute_id and d.id=b.attribute_details_id and d.config_details like '%${config_details}%' and e.id=a.category_id and\n" +
            "a.commodity_oricture=f.id and e.category_name='${category_name}' and a.id=h.goods_id and i.id=h.config_id and j.id=h.config_details_id and j.attribute like '%${attribute}%'")
    List<TCommodity>sp(String category_name,String config_details,String attribute);


    @Select("SELECT COUNT(DISTINCT a.id)\n" +
            "FROM t_commodity a,t_selected_attribute b,t_category_config c,t_config_details d,`t_commodity_category` e, t_picture f,\n" +
            "t_selected_config h,t_category_attribute i,t_attribute_details j\n" +
            "where a.id=b.goods_id and c.id=b.attribute_id and d.id=b.attribute_details_id and d.config_details like '%${config_details}%' and e.id=a.category_id and\n" +
            "a.commodity_oricture=f.id and e.category_name='${category_name}' and a.id=h.goods_id and i.id=h.config_id and j.id=h.config_details_id and j.attribute like '%${attribute}%'")
    int sp1(String category_name,String config_details,String attribute);

    //销量排序
    @Select("SELECT DISTINCT a.id,a.commodity_name,a.commodity_description,a.commodity_price,a.status,a.sales_volume,f.picture_path_name\n" +
            "FROM t_commodity a,t_selected_attribute b,t_category_config c,t_config_details d,`t_commodity_category` e, t_picture f,\n" +
            "t_selected_config h,t_category_attribute i,t_attribute_details j\n" +
            "where a.id=b.goods_id and c.id=b.attribute_id and d.id=b.attribute_details_id and d.config_details like '%${config_details}%' and e.id=a.category_id and\n" +
            "a.commodity_oricture=f.id and e.category_name='${category_name}' and a.id=h.goods_id and i.id=h.config_id and j.id=h.config_details_id and j.attribute like '%${attribute}%'  ORDER BY a.sales_volume DESC")
    List<TCommodity>px(String category_name,String config_details,String attribute);

    //销量排序
    @Select("SELECT DISTINCT a.id,a.commodity_name,a.commodity_description,a.commodity_price,a.status,a.sales_volume,f.picture_path_name\n" +
            "FROM t_commodity a,t_selected_attribute b,t_category_config c,t_config_details d,`t_commodity_category` e, t_picture f,\n" +
            "t_selected_config h,t_category_attribute i,t_attribute_details j\n" +
            "where a.id=b.goods_id and c.id=b.attribute_id and d.id=b.attribute_details_id and d.config_details like '%${config_details}%' and e.id=a.category_id and\n" +
            "a.commodity_oricture=f.id and e.category_name='${category_name}' and a.id=h.goods_id and i.id=h.config_id and j.id=h.config_details_id and j.attribute like '%${attribute}%'  and a.commodity_price BETWEEN '${xiao}' and '${da}'")
    List<TCommodity>jiage(String category_name,String config_details,String attribute,int xiao,int da);
}
